/*
 * Copyright 2007 Mojo Chen
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */
var linkxdDb = {
	
	linkTagMain:1,
	
	linkTagMy:2,
	
	linkTypeMain:1,
	
	linkTypeGood:2,
	
	tbArticleName : 'article',

	tbArticleSQL : 'CREATE TABLE article(id INTEGER PRIMARY KEY, url TEXT,title TEXT, summary TEXT, type INTEGER, time INTEGER, count INTEGER)',
	
	tbLinkName : 'link',
	
	tbLinkSQL : 'CREATE TABLE link(id INTEGER PRIMARY KEY, url TEXT,title TEXT, summary TEXT, type INTEGER, time INTEGER, count INTEGER)',
			
	tbTagName : 'tag',
	
	tbTagSQL :'CREATE TABLE tag(id INTEGER PRIMARY KEY, name TEXT,note TEXT, type INTEGER)',
	
	tbLink2TagName : 'link2tag',
	
	tbLink2TagSQL :'CREATE TABLE link2tag(link_id INTEGER , tag_id INTEGER)',
	
	tbLastInsertSQL :'CREATE TABLE last_insert (row_id INTEGER)',
	
	defaultDoc: '起:開端<br/>最近看到一篇...<br/><br/>' +
			'承:承接上文並加以申述<br/>感到世風日下 XD ...<br/><br/' +
			'>轉:轉折，從正面、反面加以立論 <br/>拿著電風扇往上吹看看 ...<br/><br/>' +
			'合:結束全文 <br/>不要來亂  ..<br/><br/>' +
			'版權填一填啦！',
	
	init: function() {
		
		this.mDBConn = linkxdXPConnect.getDbConnect();
	
		//  SELECT name FROM sqlite_master WHERE type='table'
		var statement = this.mDBConn.createStatement("SELECT name FROM sqlite_master WHERE type='table'");
		var isCreateTable = true;
		
		while (statement.executeStep()) {
			var tbname = statement.getString(0);
			if(tbname == this.tbLinkName){
				isCreateTable = false;
				break;
				
			}
		}
		
		if(isCreateTable){
			this.mDBConn.executeSimpleSQL(this.tbLinkSQL);
			this.mDBConn.executeSimpleSQL(this.tbArticleSQL);
			this.mDBConn.executeSimpleSQL(this.tbTagSQL);
			this.mDBConn.executeSimpleSQL(this.tbLink2TagSQL);
			this.mDBConn.executeSimpleSQL(this.tbLastInsertSQL);
			// insert default 4 tag
			// 1 = main
			// 2=  my
			// 3 = good
			// 4 = hot
			this.mDBConn.executeSimpleSQL('INSERT INTO tag(id, name,note,type) values(1, "linkxd.main","linkxd main tag",1)');
			this.mDBConn.executeSimpleSQL('INSERT INTO tag(id , name,note,type) values(2, "linkxd.my","linkxd my tag",1)');
			this.mDBConn.executeSimpleSQL('INSERT INTO tag(id , name,note,type) values(3, "linkxd.good","linkxd good tag",1)');
			this.mDBConn.executeSimpleSQL('INSERT INTO tag(id , name,note,type) values(4, "linkxd.hot","linkxd hot tag",1)');
			this.mDBConn.executeSimpleSQL('INSERT INTO last_insert values (-1)'); 
			this.loadFakeData();
		}
		
		//dump('linkTypeMain : '+ this.linkTypeMain);
 },
 	
 	loadFakeData:function(){
 		
 		this.mDBConn.executeSimpleSQL('insert into link(url,title,summary,type,time,count) values("http://linkxd.com/","LinkXD","Have a Nice Day-ay-ay",1,datetime("now"),0)'); 
 		this.mDBConn.executeSimpleSQL("insert into article(url,title,summary,type,time,count) values('http://linkxd.com/','Article Example','" +
 				this.defaultDoc +
 				"',1,datetime('now'),0)"); 
		this.mDBConn.executeSimpleSQL('insert into link2tag values(1,1)');
		
		// select link with tag_id 2
		// select * from link where id in (select link_id from link2tag where tag_id = 2)
		
		// select tag with link_id 1
		// select * from tag where id in (select tag_id from link2tag where link_id = 1)
 	},
 
	xinsertLink: function(title, url, summary, type) {
		var statement = this.mDBConn.createStatement("INSERT INTO link(title,summary,url,type) VALUES (?1,?2,?3,?4)");
		statement.bindUTF8StringParameter(0, title);
		statement.bindUTF8StringParameter(1, summary);
		statement.bindStringParameter(2, url);
		statement.bindInt32Parameter(3, type);
		statement.execute();
	},
	
	insertTagLink: function(title, url, summary, tagId) {
		var ourTransaction = false;
		if (this.mDBConn.transactionInProgress) {
			ourTransaction = true;
			this.mDBConn.beginTransactionAs(mDBConn.TRANSACTION_DEFERRED);
		}
		// default type is 1
		var statement = this.mDBConn.createStatement("INSERT INTO link(title,summary,url,type,time,count) VALUES (?1,?2,?3,1,datetime('now'),0)");
		statement.bindUTF8StringParameter(0, title);
		statement.bindUTF8StringParameter(1, summary);
		statement.bindStringParameter(2, url);
		statement.execute();
		// TODO any better way ?
		// http://osdir.com/ml/db.sqlite.general/2004-04/msg00047.html
		this.mDBConn.executeSimpleSQL('UPDATE last_insert SET row_id = last_insert_rowid()');
		statement = this.mDBConn.createStatement("INSERT INTO link2tag(link_id,tag_id) VALUES ((SELECT row_id FROM last_insert),?1)");
		statement.bindInt32Parameter(0, tagId);
		
		statement.execute();
		if (ourTransaction)
		  this.mDBConn.commitTransaction();
	},
	
	insertArticle: function(title, url, summary) {
		// default type is 1
		
		//alert(summary);
	try{
		var statement = this.mDBConn.createStatement("INSERT INTO article(title,summary,url,type,time,count) VALUES (?1,?2,?3,1,datetime('now'),0)");
		statement.bindUTF8StringParameter(0, title);
		statement.bindUTF8StringParameter(1, summary);
		statement.bindStringParameter(2, url);
		statement.execute();
	}catch(err){
		alert(err);
	}
		//alert('Save Done.');
	},
	
	clear: function() {
    	this.mDBConn.executeSimpleSQL("DELETE FROM link");
    },
    
    // TODO ? remove this link id from link2tag table.?
    remove: function(id) {
		var statement = this.mDBConn.createStatement("DELETE FROM link where id=?1");
		statement.bindInt32Parameter(0, id*1);
		statement.execute();
	},
	
    removeArticle: function(id) {
		var statement = this.mDBConn.createStatement("DELETE FROM article where id=?1");
		statement.bindInt32Parameter(0, id*1);
		statement.execute();
	},
	
	getLinks: function(type){
  		// main type = 1
  		// good type = 2
		var statement = this.mDBConn.createStatement("SELECT * FROM link where type=?1");
		statement.bindInt32Parameter(0, type);
	
		var objs = [];
		while (statement.executeStep()) {
			var obj = new Object();
			obj.id = statement.getInt32(0); // id 
			obj.url = statement.getString(1);
			obj.title = statement.getUTF8String(2);
			obj.summary = statement.getUTF8String(3);
			obj.type = statement.getInt32(4); // type 
			objs.push(obj);
		}
		return objs;
  	},
  	
	getTagLinks: function(tagId){
		// select * from link where id in (select link_id from link2tag where tag_id = 2)
		var statement = this.mDBConn.createStatement("SELECT * FROM link where id in (select link_id from link2tag where tag_id = ?1)");
		statement.bindInt32Parameter(0, tagId);
	
		var objs = [];
		while (statement.executeStep()) {
			var obj = new Object();
			obj.id = statement.getInt32(0); // id 
			obj.url = statement.getString(1);
			obj.title = statement.getUTF8String(2);
			obj.summary = statement.getUTF8String(3);
			obj.type = statement.getInt32(4); // type 
			objs.push(obj);
		}
		return objs;
  	},
  	
  	getArticles: function(){
		var statement = this.mDBConn.createStatement("SELECT * FROM article");
		var objs = [];
		while (statement.executeStep()) {
			var obj = new Object();
			obj.id = statement.getInt32(0); // id 
			obj.url = statement.getString(1);
			obj.title = statement.getUTF8String(2);
			obj.summary = statement.getUTF8String(3);
			obj.type = statement.getInt32(4); // type 
			objs.push(obj);
		}
		return objs;
  	},
  	
	getTags: function(){
		var statement = this.mDBConn.createStatement("SELECT * FROM tag");
		var objs = [];
		while (statement.executeStep()) {
			var obj = new Object();
			obj.id = statement.getInt32(0); // id 
			obj.name = statement.getUTF8String(1);
			obj.note = statement.getUTF8String(2);
			objs.push(obj);
		}
		return objs;
  	}
};
